﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Web;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Reflection;
using System.Diagnostics;
using System.Collections;
using System.Configuration;

namespace GLLog.MSSQL
{
    public partial class SqlOp
    {
        #region 公共属性
        //静态实例化
        private static SqlOp _Instance;
        public static SqlOp Instance
        {
            get
            {
                if (_Instance == null)
                {
                    _Instance = new SqlOp();
                }
                return _Instance;
            }
        }

        private string _ConnStr;
        public string ConnStr
        {
            get
            {
                return _ConnStr;
            }
            set
            {
                _ConnStr = value;
            }
        }

        private bool _IsChkSql;
        public bool IsChkSql
        {
            get
            {
                return _IsChkSql;
            }
            set
            {
                _IsChkSql = value;
            }
        }

        private string _ChkSql;
        public string ChkSql
        {
            get
            {
                return _ChkSql;
            }
            set
            {
                _ChkSql = value;
            }
        }
        #endregion

        #region 私有属性
        private SqlConnection _Conn;
        public SqlConnection Conn
        {
            get
            {
                return _Conn;
            }
            set
            {
                _Conn = value;
            }
        }
        #endregion

        #region 构造函数
        public SqlOp()
        {
            //ConnStr = ConfigurationManager.AppSettings["ConnStr"].ToString();
            ConnStr = "server=192.168.70.44;Pooling=true;uid=sa;pwd=rising;database=GLMVC;Connect Timeout=30";
            IsChkSql = false;
            ChkSql = ConfigurationManager.AppSettings["ChkSql"];
        }

        public SqlOp(string ConfigConn)
        {
            ConnStr = ConfigurationManager.AppSettings[ConfigConn];
            IsChkSql = true;
            ChkSql = ConfigurationManager.AppSettings["ChkSql"];
        }

        public SqlOp(string ConfigConn, int TimeOut)
        {
            ConnStr = ConfigurationManager.AppSettings[ConfigConn];
            ConnStr = ConnStr.Replace("Connect Timeout=30", "Connect Timeout=" + TimeOut + "");
            IsChkSql = true;
            ChkSql = ConfigurationManager.AppSettings["ChkSql"];
        }

        public SqlOp(string ConfigConn, bool Config)
        {
            if (Config)
            {
                ConnStr = ConfigurationManager.AppSettings[ConfigConn];
                IsChkSql = true;
                ChkSql = ConfigurationManager.AppSettings["ChkSql"];
            }
            else
            {
                ConnStr = ConfigConn;
                IsChkSql = true;
                ChkSql = ConfigurationManager.AppSettings["ChkSql"];
            }
        }
        #endregion

        public void Open()
        {
            Conn = new SqlConnection(ConnStr);
            if (Conn.State == ConnectionState.Closed)
            {
                Conn.Open();
            }
        }

        public void Close()
        {
            if (Conn != null && Conn.State == ConnectionState.Open)
            {
                Conn.Close();
            }
        }

        public void Dispose()
        {
            if (Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
            }
            GC.Collect();
        }

        public DataSet GetDatasSet(string cmdText, params SqlParameter[] cmdParms)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                DataSet ds = new DataSet();
                SqlDataAdapter sqlDa = new SqlDataAdapter(cmdText, Conn);
                sqlDa.SelectCommand.CommandType = CommandType.StoredProcedure;
                if (cmdParms != null)
                {
                    foreach (SqlParameter parm in cmdParms)
                    {
                        sqlDa.SelectCommand.Parameters.Add(parm);
                    }
                }
                sqlDa.Fill(ds, "dt");
                return ds;
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                throw (new Exception(ex.Message));
            }
            finally
            {
                Close();
            }
        }

        public DataSet GetExcelDatasSet(string FilePath, string ExcSql)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";" + "Extended Properties='Excel 8.0;HDR=yes;IMEX=1';";
            if (FilePath.Contains(".xlsx"))
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + FilePath + "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
            }
            DataSet ds = null;
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                conn.Open();
                OleDbDataAdapter myCommand = new OleDbDataAdapter(ExcSql, strConn);
                ds = new DataSet();
                myCommand.Fill(ds, "dt");

            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                throw (new Exception(ex.Message));
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return ds;
        }

        public DataSet GetDatasSet(string excSql, int Num, params SqlParameter[] cmdParms)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                DataSet ds = new DataSet();
                SqlDataAdapter sqlDa = new SqlDataAdapter(excSql, Conn);
                sqlDa.SelectCommand.CommandTimeout = 600;
                if (cmdParms != null)
                {
                    foreach (SqlParameter parm in cmdParms)
                    {
                        sqlDa.SelectCommand.Parameters.Add(parm);
                    }
                }
                sqlDa.Fill(ds, "dt");
                return ds;
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                throw (new Exception(ex.Message));
            }
            finally
            {
                Close();
            }
        }

        public DataSet GetDatasSet(string excSql)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                DataSet ds = new DataSet();
                SqlDataAdapter adp = new SqlDataAdapter(excSql, Conn);
                adp.Fill(ds, "dt");
                adp = null;
                return ds;
            }
            catch (Exception ex)
            {
                // ErrEvent(ex);
                throw (new Exception(ex.Message));
            }
            finally
            {
                Close();
            }

        }

        public DataSet GetDatasSet(string excSql, int TimeOut)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                DataSet ds = new DataSet();
                SqlDataAdapter adp = new SqlDataAdapter(excSql, Conn);
                adp.SelectCommand.CommandTimeout = TimeOut;
                adp.Fill(ds, "dt");
                adp = null;
                return ds;
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
            }
        }

        public DataSet GetDatasSet(string excSql, int i, int j)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                DataSet ds = new DataSet();
                SqlDataAdapter adp = new SqlDataAdapter(excSql, Conn);
                adp.Fill(ds, i, j, "dt");
                adp = null;
                return ds;
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
            }
        }

        public int ExecuteNonQuery(string excSql)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                SqlCommand cmd = new SqlCommand(excSql, Conn);
                int val = cmd.ExecuteNonQuery();
                cmd = null;
                return val;
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
            }
        }

        public int ExecuteNonQuery(string excSql, int Num, params SqlParameter[] cmdParms)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                SqlCommand cmd = new SqlCommand(excSql, Conn);
                if (cmdParms != null)
                {
                    foreach (SqlParameter parm in cmdParms)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }
                int val = cmd.ExecuteNonQuery();
                cmd = null;
                return val;
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
            }
        }

        public int ExecuteNonQuery(string cmdText, params SqlParameter[] cmdParms)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = cmdText;
                if (cmdParms != null)
                {
                    foreach (SqlParameter parm in cmdParms)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd = null;
                return val;
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
            }
        }

        public string ExecuteNonQuery(int OutNum, string cmdText, params SqlParameter[] cmdParms)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = cmdText;
                if (cmdParms != null)
                {
                    foreach (SqlParameter parm in cmdParms)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd = null;
                return cmdParms[cmdParms.Length - 1].Value.ToString();
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
            }
        }

        public SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] cmdParms)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = cmdText;
                if (cmdParms != null)
                {
                    foreach (SqlParameter parm in cmdParms)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd = null;
                return dr;
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                throw new Exception(ex.Message);
            }
        }

        public SqlDataReader ExecuteReader(string excSql)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                SqlCommand cmd = new SqlCommand(excSql, Conn);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd = null;
                return dr;
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                throw new Exception(ex.Message);
            }
        }

        public object ExecuteScalar(string excSql)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                SqlCommand cmd = new SqlCommand(excSql, Conn);
                object obj = cmd.ExecuteScalar();
                cmd = null;
                return obj;
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
            }
        }

        public object ExecuteScalar(string excSql, SqlParameter[] cmdParms)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                SqlCommand cmd = new SqlCommand(excSql, Conn);
                cmd.CommandType = CommandType.StoredProcedure;
                if (cmdParms != null)
                {
                    foreach (SqlParameter parm in cmdParms)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }
                object obj = cmd.ExecuteScalar();
                cmd = null;
                return obj;
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
            }
        }

        public object ExecuteScalar(string excSql, int Num, SqlParameter[] cmdParms)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                SqlCommand cmd = new SqlCommand(excSql, Conn);
                if (cmdParms != null)
                {
                    foreach (SqlParameter parm in cmdParms)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }
                object obj = cmd.ExecuteScalar();
                cmd = null;
                return obj;
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
            }
        }

        public void ExecuteSqlTran(string[] SQLStringList, int TimeOut)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Conn;
                cmd.CommandTimeout = TimeOut;
                SqlTransaction tx = Conn.BeginTransaction();
                cmd.Transaction = tx;
                string strsql = "";
                for (int n = 0; n < SQLStringList.Length; n++)
                {
                    strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();
                strsql = null;
                cmd = null;
                tx = null;
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                //System.Web.HttpContext.Current.Response.Write(ex.Message);
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
            }
        }

        public void ExecuteSqlTran(ArrayList SQLStringList, int TimeOut)
        {
            try
            {
                if (IsChkSql)
                {
                    InSql();
                }
                Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Conn;
                cmd.CommandTimeout = TimeOut;
                SqlTransaction tx = Conn.BeginTransaction();
                cmd.Transaction = tx;
                string strsql = "";
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();
                strsql = null;
                cmd = null;
                tx = null;
            }
            catch (Exception ex)
            {
                ErrEvent(ex);
                //System.Web.HttpContext.Current.Response.Write(ex.Message);
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
            }
        }

        public void ErrEvent(Exception ex)
        {

        }

        public void InSql()
        {
            
        }
    }
}
